﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;

#if MS_ACCESS
    using System.Data.OleDb;
#else
    using MySql.Data.MySqlClient;
#endif

using StonePonyMVC.Models;using System.Data;
using StonePonyMVC.StonePony.Classes;
using System.IO;
using System.Data.OleDb;
using System.Drawing;

namespace StonePonyMVC.StonePony.Services
{
    public class ImageService : IImageService
    {
        private readonly string _connectionString;
        private readonly string _smallImageDir;
        private readonly string _largeImageDir;
        private readonly string _categoryImageDirectory;
        private readonly int _selectedCategoryID;

        private bool _displayAnon;
        private int _trueValue;

        /// <summary>
        /// Read connection string from <code>Web.config</code> 
        /// </summary>
        public ImageService()
        {
            _connectionString = ConfigurationManager.AppSettings["StonePonyServices"];
            string tmpTrueValue = (ConfigurationManager.AppSettings["TrueValue"] ?? "1").ToString();
            if (!Int32.TryParse(tmpTrueValue, out _trueValue))
                _trueValue = 1;

            _smallImageDir = ConfigurationManager.AppSettings["SmallImageDir"];
            _largeImageDir = ConfigurationManager.AppSettings["LargeImageDir"];
            _categoryImageDirectory = ConfigurationManager.AppSettings["CategoryImageDirectory"];
            string tmp = ConfigurationManager.AppSettings["SelectedCategoryID"];
            if (String.IsNullOrEmpty(tmp) || !Int32.TryParse(tmp, out _selectedCategoryID))
                _selectedCategoryID = 0;
            _displayAnon = true;
        }

        #region IImageService Members

        /// <summary>
        /// Prepares list with images
        /// </summary>
        /// <param name="url">Home url</param>
        /// <returns>list with images</returns>
        public IEnumerable<Classes.ImageDescriptor> GetAllImages(string url, HttpServerUtilityBase server, string selectedCategoryName)
        {
            IEnumerable<Classes.ImageDescriptor> rzlt;
            try
            {
                const string anonWebQry = "SELECT * FROM anonwebqry {0};";
                string whereCond = String.IsNullOrEmpty(selectedCategoryName)
                    ? String.Empty
                    : String.Format("WHERE category LIKE '{0}'", selectedCategoryName);
                using (var connection = new MySqlConnection(_connectionString))
                {
                    var command = new MySqlCommand(String.Format(anonWebQry, whereCond), connection);
                    command.CommandType = System.Data.CommandType.Text;
                    connection.Open();
                    var reader = command.ExecuteReader();
                    rzlt = GetAllImages(reader, url, server, null);
                    reader.Close();
                }
                return rzlt;
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                return new List<Classes.ImageDescriptor>();
            }
        }
        public IEnumerable<ImageDescriptor> GetEnquiredImages(string url, HttpServerUtilityBase server, long sessionId)
        {
            IEnumerable<Classes.ImageDescriptor> rzlt;
            try
            {
                const string anonWebQry = "SELECT * FROM anonwebqry WHERE item_code IN (" +
                            "SELECT item_code FROM enquiries WHERE session_id = @session_id AND " +
                            "canceled <> @canceled AND emailed <> @emailed)";                    
                
                using (var connection = new MySqlConnection(_connectionString))
                {
                    var command = new MySqlCommand(anonWebQry, connection);
                    command.CommandType = System.Data.CommandType.Text;
                    command.Parameters.AddWithValue("@session_id", sessionId);
                    command.Parameters.AddWithValue("@canceled", _trueValue);
                    command.Parameters.AddWithValue("@emailed", _trueValue);
                    connection.Open();
                    var reader = command.ExecuteReader();
                    rzlt = GetAllImages(reader, url, server, null);
                    reader.Close();
                }
                return rzlt;
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                return new List<Classes.ImageDescriptor>();
            }
        }

        public IEnumerable<CategoryImageDescriptor> GetCategoryImages(string url, HttpServerUtilityBase server, 
                                                                      int? selectedCategoryId)
        {
            IList<Classes.CategoryImageDescriptor> rzlt;
            try
            {
                const string sql = "SELECT * FROM item_category;";
                using (var connection = new MySqlConnection(_connectionString))
                {
                    var command = new MySqlCommand(sql, connection);
                    command.CommandType = System.Data.CommandType.Text;
                    connection.Open();
                    var reader = command.ExecuteReader();
                    rzlt = GetAllCategoryDescriptions(reader, url, server, selectedCategoryId ?? _selectedCategoryID);
                    reader.Close();
                }
                bool isItemSelected = rzlt.Any(x => x.ClassName == "Selected");
                var allCategories = new Classes.CategoryImageDescriptor()
                {
                    Cat = 0,
                    CatName = "All",
                    ClassName = isItemSelected ? "Common" : "Selected",
                };
                rzlt.Insert(0, allCategories);
                return rzlt;
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                return new List<Classes.CategoryImageDescriptor>();
            }
        }

        public Classes.ImageDescriptor GetImage(string url, HttpServerUtilityBase server, int itemCode, string pathModifier = null)
        {
            IList<Classes.ImageDescriptor> rzlt;
            try
            {
                const string anonWebQry = "SELECT * FROM anonwebqry WHERE item_code = @ItemCode;";
                using (var connection = new MySqlConnection(_connectionString))
                {
                    var command = new MySqlCommand(anonWebQry, connection);
                    command.CommandType = System.Data.CommandType.Text;
                    command.Parameters.AddWithValue("@DisplayAnon", _displayAnon ? _trueValue : 0);
                    command.Parameters.AddWithValue("@ItemCode", itemCode);
                    connection.Open();
                    var reader = command.ExecuteReader();
                    rzlt = GetAllImages(reader, url, server, pathModifier);
                    reader.Close();
                }

                return rzlt.Count < 1 
                    ? new Classes.ImageDescriptor()
                        {
                            ItemCode = -1,
                            ItemTitle = String.Format("Error: Image {0} was not found.", itemCode)
                        }
                    : rzlt[0];
                }
            catch (Exception ex)
            {
                return new Classes.ImageDescriptor()
                    {
                        ItemCode = -1,
                        ItemTitle = ex.Message
                    };
            }
        }

        /// <summary>
        /// Writes data to enquiries table
        /// </summary>
        /// <param name="request">Request <see cref="SubmitEnquiryRequestModel"/></param>
        /// <returns>Response <see cref="SubmitEnquiryResponseModel"/></returns>
        public SubmitEnquiryResponseModel WriteEnquiry(SubmitEnquiryRequestModel request)
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                MySqlTransaction currentTrans = null;
                try
                {
                    connection.Open();
                    currentTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                    if (!ItemCodeIsvalid(request.ItemCode, connection, currentTrans))
                        throw new Exception(String.Format("Item code '{0}' is invalid.", request.ItemCode));

                    long sessionId = getSessionId(request.SessionId, connection, currentTrans);
                    int insertCount = insertEnquiries(sessionId, connection, currentTrans, request);
                    int recordsInSession = CountEnquiries(sessionId, connection, currentTrans);

                    currentTrans.Commit();
                    connection.Close();
                    return new SubmitEnquiryResponseModel()
                    {
                        Success = true,
                        SessionId = sessionId.ToString(),
                        TotalEnquiries = recordsInSession,
                        ErrorMessage = "OK"
                    };
                }
                catch (Exception ex)
                {
                    if (currentTrans != null)
                        currentTrans.Rollback();
                    return new SubmitEnquiryResponseModel()
                    {
                        Success = false,
                        SessionId = String.Empty,
                        TotalEnquiries = 0,
                        ErrorMessage = ex.Message
                    };
                }
            }
        }

        public SubmitEnquiryResponseModel SendEnquiry(long sessionId)
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                MySqlTransaction currentTrans = null;
                try
                {
                    connection.Open();
                    currentTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted);

                    int updateCount = CommitEnquiries(sessionId, connection, currentTrans);

                    currentTrans.Commit();
                    connection.Close();
                    return new SubmitEnquiryResponseModel()
                    {
                        Success = true,
                        SessionId = "-1",
                        TotalEnquiries = updateCount,
                        ErrorMessage = "OK"
                    };
                }
                catch (Exception ex)
                {
                    if (currentTrans != null)
                        currentTrans.Rollback();
                    return new SubmitEnquiryResponseModel()
                    {
                        Success = false,
                        SessionId = String.Empty,
                        TotalEnquiries = 0,
                        ErrorMessage = ex.Message
                    };
                }
            }
        }

        /// <summary>
        /// Marks enquiries as canceled
        /// </summary>
        /// <param name="sessionId">Session ID</param>
        /// <returns>Response <see cref="SubmitEnquiryResponseModel"/></returns>
        public SubmitEnquiryResponseModel ClearEnquiry(long sessionId)
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                MySqlTransaction currentTrans = null;
                try
                {
                    connection.Open();
                    currentTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted);

                    int updateCount = CancelEnquiries(sessionId, connection, currentTrans);

                    currentTrans.Commit();
                    connection.Close();
                    return new SubmitEnquiryResponseModel()
                    {
                        Success = true,
                        SessionId = "-1",
                        TotalEnquiries = updateCount,
                        ErrorMessage = "OK"
                    };
                }
                catch (Exception ex)
                {
                    if (currentTrans != null)
                        currentTrans.Rollback();
                    return new SubmitEnquiryResponseModel()
                    {
                        Success = false,
                        SessionId = String.Empty,
                        TotalEnquiries = 0,
                        ErrorMessage = ex.Message
                    };
                }
            }
        }

        public SubmitEnquiryResponseModel DeleteEnquiry(long sessionId, int itemCode)
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                MySqlTransaction currentTrans = null;
                try
                {
                    connection.Open();
                    currentTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted);

                    int updateCount = CancelEnquiry(sessionId, itemCode, connection, currentTrans);

                    currentTrans.Commit();
                    connection.Close();
                    return new SubmitEnquiryResponseModel()
                    {
                        Success = true,
                        SessionId = "-1",
                        TotalEnquiries = updateCount,
                        ErrorMessage = "OK"
                    };
                }
                catch (Exception ex)
                {
                    if (currentTrans != null)
                        currentTrans.Rollback();
                    return new SubmitEnquiryResponseModel()
                    {
                        Success = false,
                        SessionId = String.Empty,
                        TotalEnquiries = 0,
                        ErrorMessage = ex.Message
                    };
                }
            }
        }

        /// <summary>
        /// Creates letter model for defined session
        /// </summary>
        /// <param name="sessionId">Session ID</param>
        /// <param name="server"><see cref="HttpServerUtilityBase"/></param>
        /// <param name="rootUrl">URL of the StonePony application</param>
        /// <returns></returns>
        public LetterModel GetLetterModel(long sessionId, HttpServerUtilityBase server, string rootUrl)
        {
            var rzlt = new LetterModel() { SessionId = sessionId };
            try
            {
                const string enquirySessionData =
                    "SELECT enquiries.session_id, enquiries.enquirer_firstName, enquiries.enquirer_lastName, " +
                           "enquiries.phone, enquiries.email_address, enquiries.wntsToSubscribe, " +
                           "anonwebqry.item_code, anonwebqry.item_shipment, anonwebqry.item_stock_code, " +
                           "anonwebqry.title AS item_title, anonwebqry.notes AS item_notes, " +
                           "anonwebqry.item_image_large, anonwebqry.item_image_small, " +
                           "anonwebqry.web_price AS web_price, anonwebqry.status AS status " +
                    "FROM enquiries " +
                        "JOIN anonwebqry ON enquiries.item_code = anonwebqry.item_code " +
                    "WHERE enquiries.session_id=@sessionId;";

                using (var connection = new MySqlConnection(_connectionString))
                {
                    var command = new MySqlCommand(enquirySessionData, connection);
                    command.CommandType = System.Data.CommandType.Text;
                    command.Parameters.AddWithValue("@sessionId", sessionId);
                    connection.Open();
                    var reader = command.ExecuteReader();
                    GetSessionImages(rzlt, reader, server, rootUrl);
                    reader.Close();
                }
                return rzlt;
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                return null;
            }
        }

        public byte[] GetModifiedImage(string imagePath)
        {
            Image image = Image.FromFile(imagePath);
            if (image == null)
                return null;

            int delta = Math.Max(12, (int)(image.Width * 2 / 10));
            int left = image.Width - delta;
            using (Graphics g = Graphics.FromImage(image))
            {
                g.FillRectangle(Brushes.Red, left, 0, delta, delta);
                var pen = new Pen(Brushes.White, 2);
                g.DrawLine(pen, left, 0, image.Width, delta);
                g.DrawLine(pen, left, delta, image.Width, 0);
            }

            var converter = new ImageConverter();
            return (byte[])converter.ConvertTo(image, typeof(byte[]));
        }

        #endregion

        #region Private methods

        private int GetSessionImages(LetterModel model, IDataReader rd, HttpServerUtilityBase server, string rootUrl = null)
        {
            int rzlt = 0;

            int iSessionId = rd.GetOrdinal("session_id");
            int iEnquirerFirstName = rd.GetOrdinal("enquirer_firstName");
            int iEnquirerLastName = rd.GetOrdinal("enquirer_lastName");
            int iPhone = rd.GetOrdinal("phone");
            int iEmailAddress = rd.GetOrdinal("email_address");
            int iWntsToSubscribe = rd.GetOrdinal("wntsToSubscribe");

            int iItemCode = rd.GetOrdinal("item_code");
            int iItemShipment = rd.GetOrdinal("item_shipment");
            int iItemStockCode = rd.GetOrdinal("item_stock_code");
            int iItemTitle = rd.GetOrdinal("item_title");
            int iItemNotes = rd.GetOrdinal("item_notes");
            int iItemImageSmall = rd.GetOrdinal("item_image_small");
            int iItemImageLarge = rd.GetOrdinal("item_image_large");
            int iItemStatus = rd.GetOrdinal("status");
            int iItemPrice = rd.GetOrdinal("web_price");

            while (rd.Read())
            {
                if (rzlt++ < 1)
                {
                    model.SessionId = Converters.SafeConvertToLong(rd[iSessionId]);
                    model.FirstName = rd.GetString(iEnquirerFirstName);
                    model.LastName = rd.GetString(iEnquirerLastName);
                    model.Phone = rd.GetString(iPhone);
                    model.Email = rd.GetString(iEmailAddress);
                    model.WantsToSubscribe = rd.GetBoolean(iWntsToSubscribe);
                }
                ImageDescriptor item = GetCurrentImage(server, rd, iItemCode, iItemShipment, iItemStockCode, iItemTitle,
                                                       iItemNotes, iItemImageSmall, iItemImageLarge, iItemStatus, iItemPrice,
                                                       null, rootUrl);
                model.Items.Add(item);
            }
            return rzlt;
        }

/*
SELECT 
 * enquiries.session_id, enquiries.enquirer_firstName, enquiries.enquirer_lastName, 
 * enquiries.phone, enquiries.email_address, enquiries.wntsToSubscribe, 
 * items.item_code, items.item_shipment, items.item_stock_code, items.item_title, items.item_notes, items.item_image_large
FROM enquiries INNER JOIN items ON enquiries.item_code=items.item_code
WHERE (enquiries.session_id=[@sessionId]);
 
 */

        private int CancelEnquiries(long sessionId, MySqlConnection connection, MySqlTransaction currentTrans)
        {
            const string updateStatement =
                "update enquiries set canceled = @Canceled " +
                "where enquiries.session_id = @SessionId AND emailed = @Emailed;";
            var updateCommand = new MySqlCommand(updateStatement, connection);

            updateCommand.Transaction = currentTrans;
            updateCommand.Parameters.AddWithValue("@Canceled", true);
            updateCommand.Parameters.AddWithValue("@SessionId", sessionId);
            updateCommand.Parameters.AddWithValue("@Emailed", false);

            return updateCommand.ExecuteNonQuery();
        }

        private int CancelEnquiry(long sessionId, int itemCode, MySqlConnection connection, MySqlTransaction currentTrans)
        {
            const string updateStatement =
                "update enquiries set canceled = @Canceled " +
                "where enquiries.session_id = @SessionId AND enquiries.item_code = @ItemCode AND enquiries.emailed = @Emailed;";
            var updateCommand = new MySqlCommand(updateStatement, connection);

            updateCommand.Transaction = currentTrans;
            updateCommand.Parameters.AddWithValue("@Canceled", true);
            updateCommand.Parameters.AddWithValue("@SessionId", sessionId);
            updateCommand.Parameters.AddWithValue("@ItemCode", itemCode);
            updateCommand.Parameters.AddWithValue("@Emailed", false);

            return updateCommand.ExecuteNonQuery();
        }

        private int CommitEnquiries(long sessionId, MySqlConnection connection, MySqlTransaction currentTrans)
        {
            const string updateStatement = "update enquiries set emailed = @Emailed where enquiries.session_id = @SessionId;";
            var updateCommand = new MySqlCommand(updateStatement, connection);

            updateCommand.Transaction = currentTrans;
            updateCommand.Parameters.AddWithValue("@Emailed", true);
            updateCommand.Parameters.AddWithValue("@SessionId", sessionId);

            return updateCommand.ExecuteNonQuery();
        }

        private int CountEnquiries(long sessionId, MySqlConnection connection, MySqlTransaction currentTrans)
        {
            const string query = "SELECT COUNT(1) FROM enquiries WHERE enquiries.session_id = @SessionId AND enquiries.canceled = @Canceled;";
            var command = new MySqlCommand(query, connection);
            command.Transaction = currentTrans;
            command.Parameters.AddWithValue("@SessionId", sessionId);
            command.Parameters.AddWithValue("@Canceled", false);

            return SafeConvertToInt(command.ExecuteScalar());
        }
        private static int SafeConvertToInt(object value)
        {
            try
            {
                return (int)value;
            }
            catch (Exception)
            {
                int rzlt;
                if (!Int32.TryParse(value.ToString(), out rzlt))
                    rzlt = 0;
                return rzlt;
            }
        }

        private long lConverter(string value)
        {
            long rzlt;
            if (!long.TryParse(value, out rzlt))
                rzlt = -1L;
            return rzlt;
        }
        private int insertEnquiries(long sessionId, MySqlConnection connection, MySqlTransaction currentTrans, SubmitEnquiryRequestModel request)
        {
            const string insertStatement = "INSERT INTO enquiries "
                 + "(session_id, item_code, enquirer_firstName, enquirer_lastName, email_address, phone, " +
                    "created_date, emailed, wntsToSubscribe) "
                 + "VALUES (@SessionId, @ItemCode, @FirstName, @LastName, @Email, @Phone, @CreatedDate, @Emailed, @Subscribe)";
            var insertCommand = new MySqlCommand(insertStatement, connection);
            insertCommand.Transaction = currentTrans;

            insertCommand.Parameters.AddWithValue("@SessionId", sessionId);
            insertCommand.Parameters.AddWithValue("@ItemCode", lConverter(request.ItemCode));
            insertCommand.Parameters.AddWithValue("@FirstName", request.FirstName ?? String.Empty);
            insertCommand.Parameters.AddWithValue("@LastName",request.LastName ?? String.Empty);
            insertCommand.Parameters.AddWithValue("@Email", request.Email ?? String.Empty);
            insertCommand.Parameters.AddWithValue("@Phone", request.Phone ?? String.Empty);
            insertCommand.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
            insertCommand.Parameters.AddWithValue("@Emailed", false);
            insertCommand.Parameters.AddWithValue("@Subscribe", request.Accept == "1" ? _trueValue : 0);

            return insertCommand.ExecuteNonQuery();
        }

        private bool ItemCodeIsvalid(string itemCode, MySqlConnection connection, MySqlTransaction currentTrans)
        {
            const string sql = "select count(*) from anonwebqry where item_code = @ItemCode";
            var countCommand = new MySqlCommand(sql, connection);
            countCommand.Transaction = currentTrans;
            countCommand.Parameters.AddWithValue("@ItemCode", lConverter(itemCode));
            var oRzlt = countCommand.ExecuteScalar();
            return oRzlt == null || oRzlt.Equals(DBNull.Value) ? false : long.Parse(oRzlt.ToString()) == 1L;
        }

        private long getSessionId(string sessionId, MySqlConnection connection, MySqlTransaction currentTrans)
        {
            long rzlt = -1L;
            bool generateNewSession;
            if (String.IsNullOrEmpty(sessionId) || !Int64.TryParse(sessionId, out rzlt))
                generateNewSession = true;
            else
                generateNewSession = rzlt <= 0;

            if (generateNewSession)
            {
                const string query = "SELECT max(session_id) + 1 AS nSessionId FROM enquiries;";
                var command = new MySqlCommand(query, connection);
                command.CommandType = System.Data.CommandType.Text;
                command.Transaction = currentTrans;
                var oRzlt = command.ExecuteScalar();
                rzlt = oRzlt == null || oRzlt.Equals(DBNull.Value) ? 1L : long.Parse(oRzlt.ToString());
            };

            return rzlt;
        }

        private IList<Classes.ImageDescriptor> GetAllImages(IDataReader rd, string url, HttpServerUtilityBase server,
                                                            string pathModifier)
        {
            var rzlt = new List<Classes.ImageDescriptor>();
            
            int iItemCode = rd.GetOrdinal("item_code");
            int iItemShipment = rd.GetOrdinal("item_shipment");
            int iItemStockCode = rd.GetOrdinal("item_stock_code");
            int iItemTitle = rd.GetOrdinal("title");
            int iItemNotes = rd.GetOrdinal("notes");
            int iItemImageSmall = rd.GetOrdinal("item_image_small");
            int iItemImageLarge = rd.GetOrdinal("item_image_large");
            int iItemStatus = rd.GetOrdinal("status");
            int iItemPrice = rd.GetOrdinal("web_price");

            while (rd.Read())
            {
                ImageDescriptor item = GetCurrentImage(server, rd, iItemCode, iItemShipment, iItemStockCode, iItemTitle,
                                                       iItemNotes, iItemImageSmall, iItemImageLarge, iItemStatus, iItemPrice,
                                                       pathModifier, null);
                rzlt.Add(item);
            }

            return rzlt;
        }
        private IList<Classes.CategoryImageDescriptor> GetAllCategoryDescriptions(
            IDataReader rd, string url, HttpServerUtilityBase server, int selectedCategoryId)
        {
            var rzlt = new List<Classes.CategoryImageDescriptor>();
            
            int iItemCode = rd.GetOrdinal("cat");
            int iItemName = rd.GetOrdinal("cat_txt");
            string imageDir = server.MapPath(_categoryImageDirectory);
            while (rd.Read())
            {
                var item = new Classes.CategoryImageDescriptor()
                {
                    Cat = rd.GetInt32(iItemCode),
                    CatName = rd.IsDBNull(iItemName) ? String.Empty : rd.GetString(iItemName),
                };
                item.ClassName = item.Cat == selectedCategoryId ? "Selected" : "Common";
                rzlt.Add(item);
            }
            return rzlt.ToList();
        }

        private ImageDescriptor GetCurrentImage(HttpServerUtilityBase server, IDataReader rd, int iItemCode, int iItemShipment, 
            int iItemStockCode, int iItemTitle, int iItemNotes, int? iItemImageSmall, int iItemImageLarge, int iItemStatus,
            int iItemPrice, string pathModifier, string rootUrl = null)
        {
            var item = new Classes.ImageDescriptor()
            {
                ItemCode = rd.GetInt32(iItemCode),
                ItemShipment = rd.GetInt32(iItemShipment),
                ItemStockCode = rd.IsDBNull(iItemStockCode) ? String.Empty : rd.GetString(iItemStockCode),
                ItemTitle = rd.IsDBNull(iItemTitle) ? String.Empty : rd.GetString(iItemTitle),
                ItemNotes = rd.IsDBNull(iItemNotes) ? String.Empty : rd.GetString(iItemNotes),
                ItemImageSmall = iItemImageSmall.HasValue && !rd.IsDBNull(iItemImageSmall.Value) ? rd.GetString(iItemImageSmall.Value) : "no_image_sm.jpg",
                ItemImageLarge = rd.IsDBNull(iItemImageLarge) ? "no_image.jpg" : rd.GetString(iItemImageLarge),
                Status = rd.IsDBNull(iItemStatus) ? String.Empty : rd.GetString(iItemStatus),
                Price = rd.IsDBNull(iItemPrice) ? String.Empty : rd.GetString(iItemPrice)
            };

            item.SetSmallImage(_smallImageDir, server, "no_image_sm.jpg", pathModifier, rootUrl);
            item.SetLargeImage(_largeImageDir, server, "no_image.jpg");
            
            return item;
        }
        #endregion
    }
}